Create Date Table , Measure And Calculated Column 8

1. Creating a Date Table:

Date = ADDCOLUMNS(
           CALENDAR(   MIN('Opportunities'[NewFormatDate]),
                       MAX( 'Opportunities'[NewFormatDate])
                    ),
            "Year", YEAR( [Date] ), 
            "Quarter Number",  INT ( FORMAT ( [Date], "q") ),
            "Quarter", "Q" & INT ( FORMAT ( [Date], "q") ),
            "Month Number", MONTH ( [Date] ),
            "Month", FORMAT ( [Date], "mmm" ),
            "Week Day Number", WEEKDAY ( [Date] ),
            "Week Day", FORMAT ( [Date], "dddd" ),
            "Year Month Number", YEAR ( [Date] ) *100 + MONTH ( [Date] ),
            "Year Month", FORMAT ( [Date], "mmm" ) & " " & YEAR ( [Date] ),
            "Year Quarter Number", YEAR ( [Date] ) * 100 + INT ( FORMAT ( [Date], "q") ),
            "Year Quarter",  "Q" & FORMAT ( [Date], "q") & "-" & YEAR ( [Date] )
            
            )

Follow the GIF to create the date table

Connect the DateTable to the opportunities Table with the date column

And the SalesRep Table to Rep table using Rep column

You will follow below Screen shots to Create your measures

create on opportunity Table

.Deal won = 
                 CALCULATE (
                    COUNT ( 'Opportunities'[Statusid]),
                        FILTER (Opportunities,Opportunities[Statusid]  = 5 ))
                                                                 
        2 .     WinCount = COUNT(Opportunities[Statusid])

3. Win Rate = DIVIDE([.Deal won],Opportunities[.WinCount])

4. .New Win Rate = IF(ISBLANK([Win Rate]),0,[Win Rate])

5. YoY% =

         VAR __PREV_YEAR = CALCULATE([Win Rate], DATEADD('Date'[Date], -1, YEAR))
          RETURN
           DIVIDE([Win Rate] - __PREV_YEAR, __PREV_YEAR)

6. YOY = SWITCH(TRUE(),[YoY%]>0,UNICHAR(9650),[YoY%] <0,UNICHAR(9660)) & ROUND ( [YoY%], 2) * 100 &"%"

  7.      .KPIPerformanceIndicatorColor = SWITCH(TRUE(),[YoY%]>0,"#1AAB40",[YoY%] <0,"#8B0000",[YoY%]=0, "#3a6c9b")

     .Deal Lost Rev = CALCULATE (SUM( Opportunities[Revenue] ), FILTER (Opportunities,Opportunities[Statusid]=10))

      .Deal won Rev = CALCULATE (SUM( Opportunities[Revenue] ), FILTER (Opportunities,Opportunities[Statusid]=5))

       .TOTal Reve = SUM(Opportunities[Revenue])

     .% Loss Revenue = DIVIDE( [.Deal Lost Rev], [.TOTal Reve], 0 )

      .% won Revenue = DIVIDE( [.Deal won Rev], [.TOTal Reve], 0 )

The Gif to create measures

Calculated Column

.Closed Revenue = if(Opportunities[Statusid]= 5,Opportunities[Revenue], BLANK())

.New Revenue = IF(ISBLANK(Opportunities[.Closed Revenue]),(0),Opportunities[.Closed Revenue])

.Lost Revenue = IF(Opportunities[Statusid] = 10, Opportunities[Revenue], 0)

For more information about the date table, please see the YouTube below.

https://www.youtube.com/watch?v=-li7sxUxEqA